import pymysql
class DAO_university:
    def __init__(self):
        pass

    def add(self, uni_id, uni_code, uni_name, uni_location, uni_subjection, uni_characteristic, uni_graduate_school, uni_self_line):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO sch_university VALUES ('%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d')" % \
              (uni_id, uni_code, uni_name, uni_location, uni_subjection, uni_characteristic, uni_graduate_school, uni_self_line)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print("insert ok")
        except:
            # 发生错误时回滚
            print("(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 查询学校院校代码是否已经爬取，已经爬取返回false，没爬取返回true
    def uni_code_is0(self, uni_name):
        cursor = self.get_conn().cursor()
        sql = 'select uni_code from sch_university where uni_name = "%s"' % uni_name
        rows = cursor.execute(sql)
        if cursor.fetchone()[0] == '0':
            return True
        else:
            return False

    # 数据库更新代码如下
    # UPDATE sch_university SET uni_code = 10558 WHERE uni_name = "中山大学"

    # 更新院校的代码（这个模块创建于2018-10-21）
    def update_uni_code(self, uni_code, uni_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        sql = "UPDATE sch_university SET uni_code = '%s' WHERE uni_name = '%s'" % (uni_code, uni_name)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print("insert ok")
        except:
            # 发生错误时回滚
            print("UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 与数据库建立连接
    def get_conn(self):

        # 里皮的阿里云服务器
        # conn = pymysql.connect(host='39.105.39.215', port=3307, user="root", passwd="qq1040256886", db="yanxin", charset="utf8")

        # 本机的数据库
        #conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="root", db="yanxin", charset="utf8")
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="123", db="kaoyanpy", charset="utf8")
        return conn

    # 查询并输出所有数据
    def sel_all(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_university'
        rows = cursor.execute(sql)
        res = cursor.fetchall()
        print('共有', rows, '条数据')
        for re in res:
            print(re)
        print('共有', rows, '条数据')

    # 待定
    def is_empty(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_university'
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 待定
    def truncate(self):
        if self.is_empty():
            print("数据为空")
        else:
            sql = "truncate table sch_university"
            conn = self.get_conn()
            conn.cursor().execute(sql)
            conn.commit()
            print("已清空数据")

    # def add(self):
    #     cursor = self.get_conn()
